import sqlite3
import re
import traceback
def sqlite_cmd(sql_cmd):
    try:
        conn = sqlite3.connect('subway_qd.sqlite3')
        c = conn.cursor()
        c.execute(sql_cmd)
        conn.commit()
        conn.close()
        return 0
    except:
        print("commit ERROR!!!:",sql_cmd)
        return -1
    
def sqlite_select_cmd(sql_cmd):
    try:
        conn = sqlite3.connect('subway_qd.sqlite3')
        c = conn.cursor()
        cursor = c.execute(sql_cmd)
        rows = cursor.fetchall()
        conn.close()
    except:
        print("select ERROR!!!:",sql_cmd)
        rows=[]
    return rows
        
def select_stations_id(station_name):
    if station_name=='NULL':
        rows=[]
    else:
        sql_cmd="select id from Stations where station_name='%s'"%(station_name)
        rows = sqlite_select_cmd(sql_cmd)
    return rows

def insert_LineStations(line_id,station_name_list):
    f_station_name=""
    i_station_name=""
    l_station_name=""
    end=len(station_name_list)
    for i in range(end):
        if i==0:
            f_station_name=station_name_list[i]
            i_station_name=station_name_list[i]
            l_station_name=station_name_list[i+1]
        elif i== end -1:
            f_station_name=station_name_list[i-1]
            i_station_name=station_name_list[i]
            l_station_name=station_name_list[i]
        else:
            f_station_name=station_name_list[i-1]
            i_station_name=station_name_list[i]
            l_station_name=station_name_list[i+1]
        # print(line_id,f_station_name,i_station_name,l_station_name)
        sql_cmd="select * from LineStations Where line_id=%s and station_id=%s"%(line_id,select_stations_id(i_station_name)[0][0])
        if sqlite_select_cmd(sql_cmd)==[]:
            sql_cmd="Insert into LineStations (line_id,station_id,previous_station_id,next_station_id) values (%s,%s,%s,%s)"%(line_id,select_stations_id(i_station_name)[0][0],select_stations_id(f_station_name)[0][0],select_stations_id(l_station_name)[0][0])
            # print(sql_cmd)
            sqlite_cmd(sql_cmd)
            
            

def add_stations_lines(f):
    i=0
    line_dir={
        'id':'',
        'name':'',
        'colour':'',
        'fromTo1':'',
        'fromTo2':'',
        'totalStations':''
    }
    for line in f.readlines():
        try:
            pattern = r"^(\d+)\s+(.*?)\s+(\d+\.\d+)\s+(\d+\.\d+)"
            match = re.match(pattern, line)
            station_name = match.group(2)
            longitude = float(match.group(3))
            latitude= float(match.group(4))
            sql_cmd="select * From Stations  Where station_name='%s'"%(station_name)
            if sqlite_select_cmd(sql_cmd)==[]:
                sql_cmd="Insert Into Stations (id,station_name,latitude,longitude) values (%s,'%s',%.8f,%.8f)"%(i,station_name,latitude,longitude)
                i=i+1
                sqlite_cmd(sql_cmd)
        except:
            pass
        try:
            id = re.search(r'id:\s*(\d+)', line)
            id_num = id.group(1)
            line_dir['id']=id_num
        except:
            pass
        try:
            name = re.search(r'name:\s*(.*)', line)
            name_string = name.group(1)
            # print(name_string)
            line_dir['name']=name_string
        except:
            pass
        try:
            colour = re.search(r'colour:\s*(.*)', line)
            colour_string = colour.group(1)
            line_dir['colour']=colour_string
        except:
            pass
        try:
            fromTo = re.search(r'fromTo:\s*(.*)', line)
            fromTo_string = fromTo.group(1)
            fromTo1= re.search(r'(.*)\s+(.*)', fromTo_string)
            fromTo_string1 = fromTo1.group(1)
            line_dir['fromTo1']=fromTo_string1
            fromTo_string2 = fromTo1.group(2)
            line_dir['fromTo2']=fromTo_string2
        except:
            pass
        try:
            totalStations = re.search(r'totalStations:\s*(\d+)', line)
            totalStations_num = totalStations.group(1)
            line_dir['totalStations']=totalStations_num
            sql_cmd="select *  From Lines  Where id=%s"%(id_num)
            if sqlite_select_cmd(sql_cmd)==[]:
                sql_cmd="Insert Into Lines (id,line_name,line_color) values(%s,'%s','%s')"%(id_num,name_string,colour_string)
                sqlite_cmd(sql_cmd)
        except:
            pass
def add_linestation(f):
    station_name_list=[]
    for line in f.readlines():
        try:
            id = re.search(r'id:\s*(\d+)', line)
            id_num = id.group(1)
        except:
            pass
        try:
            pattern = r"^(\d+)\s+(.*?)\s+(\d+\.\d+)\s+(\d+\.\d+)"
            match = re.match(pattern, line)
            station_name = match.group(2)
            station_name_list.append(station_name)
            # print(station_name)
        except:
            if station_name_list:
                insert_LineStations(id_num,station_name_list)
                station_name_list=[]
            pass
   
if __name__=='__main__':
    with open('lineQD.txt', mode='r',encoding='utf-8') as f:
        # f=open('lineQD.txt', mode='r',encoding='utf-8')
        add_stations_lines(f)
        f.close()
    with open('lineQD.txt', mode='r',encoding='utf-8') as f:
        add_linestation(f)
        f.close()
    print("如果没有提示，那就是最好的提示")